/**
 ****************************************************************************
 * Copyright (C) Marcelo F. Ochoa. All rights reserved.                      *
 * ------------------------------------------------------------------------- *
 * This software is published under the terms of the Apache Software License *
 * version 1.1, a copy of which has been included  with this distribution in *
 * the LICENSE file.                                                         *
 */

package com.prism.demo.lite;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;

import oracle.xml.sql.query.OracleXMLQuery;

import com.prism.toolkit.Jowa;
import com.prism.toolkit.JowaArray;
import com.prism.toolkit.JowaImage;
import com.prism.toolkit.JowaUtil;
import com.prism.toolkit.JwpgDocLoad;
import com.prism.toolkit.Jxtp;

class DEMOj {
    public static void STARTUP() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "DB Prism 2.0.0 / Cocoon 2 Demo Page");
        Jxtp.tag("para", "With Java Stored Procedures support");
        Jxtp.tag("para", "Use Jxtp toolkit and Oracle XML Utility for Java 9.0.2+");
        Jxtp.tag("para", "iso-8859-1 text \u00e1\u00e9\u00ed\u00f3\u00fa");
        Jxtp.tagOpen("table");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World", "href=\"DEMOj.hello\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Shows overloading stored procedures support (version 1)");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World", "href=\"DEMOj.hello?name=Marcelo&amp;n=3\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Shows overloading stored procedures support (version 2)");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World", "href=\"DEMOj.hello?name=Marcelo&amp;n=3&amp;msgs=linea1&amp;msgs=linea2\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Shows overloading stored procedures support (version 3)");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World with cookies", "href=\"DEMOj.cookie\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Same as Hello World but with Cookies definition");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Hello World with cache control", "href=\"DEMOj.expires\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Same as Hello World but with Cache-Control header");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Redirect support", "href=\"DEMOj.redirect\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Redirect to xml.apache.org");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Set Status Header", "href=\"DEMOj.status\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Sent a header line Status: 200 test");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Not Authorized support", "href=\"DEMOj.notauthorized\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Sent Not Authorized");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Excel Output", "href=\"DEMOj.excel\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Query using SQLJ translator, uses Excel Stylesheet");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Flexible parameters support", "href=\"!DEMOj.flex?x=1&amp;y=2&amp;x=3\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Calls the stored procedures with this arguments x=1&amp;y=2&amp;x=3");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Input Form Demo", "href=\"DEMOj.input\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Form example with form image support");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Query Example using Oracle XSU", "href=\"DEMOj.list\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Calls Oracle XML Query Utility to make the XML result");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Query Example using Jxtp", "href=\"DEMOj.list_jxtp\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Query using standard server side JDBC and printing with Jxtp toolkit to compare performance with Oracle XML Query toolkit");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Upload Form", "href=\"DEMOj.upload_form\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Upload/Download functionality, stores in the content table");
        Jxtp.tagClose("tr");
        Jxtp.tagOpen("tr");
        Jxtp.tagOpen("td");
        Jxtp.tag("a", "Full qualify support", "href=\"/dpls/sample/sys.owa_util.print_cgi_env\"");
        Jxtp.tagClose("td");
        Jxtp.tag("td", "Using syntax schema.package.stored_procedure");
        Jxtp.tagClose("tr");
        Jxtp.tagClose("table");
        Jxtp.tagClose("page");
    }

    public static void HELLO() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my first Prism-Cocoon code, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void HELLO(String name, int n) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello " + name);
        Jxtp.tagOpen("content");
        for (int i = 0; i < n; i++)
            Jxtp.tag("para", "line number ! i = " + i);
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void HELLO(String name, int n, byte[] msgs) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello " + name);
        Jxtp.tagOpen("content");
        for (int i = 0; i < n; i++)
            Jxtp.tag("para", "line number ! i = " + i);
        try {
            JowaArray msgArray = new JowaArray(msgs);
            for (int i = 0; i < msgArray.getSize(); i++)
                Jxtp.tag("para", "msg line = " + i + " " + msgArray.getValueAt(i));
        } catch (Exception e) {
            Jxtp.tag("para", "Error in casting JowaArray argument");
        }
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void INPUT() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Sample Form");
        Jxtp.tagOpen("form", "action='DEMOj.output' method='POST'");
        Jxtp.tag("para", "Please enter a Text String:");
        Jxtp.tag("input", "", "title='Name' type='hidden' name='b' value='default'");
        Jxtp.tag("input", "", "title='Name' type='text' name='b'");
        Jxtp.tag("input", "", "type='image' name='OK' src='icons/ehome.gif'");
        Jxtp.tagClose("form");
        Jxtp.tagClose("page");
    }

    public static void DOWNLOAD() {
        String scriptName = Jowa.GETCGIVAR("SCRIPT_NAME") + Jowa.GETCGIVAR("PATH_INFO");
        JwpgDocLoad.DOWNLOADFILE(scriptName.substring(scriptName.indexOf(Jowa.GETCGIVAR("DOC_ACCESS_PATH")) +
            Jowa.GETCGIVAR("DOC_ACCESS_PATH").length() + 1));
    }

    public static void DOCS_LOAD(Connection sqlconn) throws SQLException {
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Documents Uploaded");
        Jxtp.tagOpen("table");
        // Create a Statement
        stmt = sqlconn.createStatement();
        // Select the lobs
        rset = stmt.executeQuery("select name,mime_type,doc_size from " + Jowa.GETCGIVAR("DOCUMENT_TABLE"));
        while (rset.next()) {
            Jxtp.tagOpen("tr");
            String name = rset.getString(1);
            Jxtp.tagOpen("td");
            Jxtp.tag("a", name, "href=\"/dpls/java/" + Jowa.GETCGIVAR("DOC_ACCESS_PATH") + "/" + name + "\"");
            Jxtp.tagClose("td");
            Jxtp.tag("td", rset.getString(2));
            Jxtp.tag("td", rset.getInt(3) + " bytes");
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        rset.close();
        stmt.close();
        Jxtp.tagClose("page");
    }

    public static void UPLOAD_FORM() {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Sample Form");
        Jxtp.tagOpen("form", "method='post' action='/dpls/java/DEMOj.upload' enctype='multipart/form-data'");
        Jxtp.tag("para", "Please select a File to Upload:");
        Jxtp.tag("input", "", "type=\"file\" name=\"binaryfiles\"");
        Jxtp.tag("input", "", "type=\"file\" name=\"binaryfiles\"");
        Jxtp.tag("input", "", "type=\"image\" name=\"OK\" src=\"icons/ehome.gif\"");
        Jxtp.tagClose("form");
        Jxtp.tag("a", "Wanna peek at what others have loaded...", "href=\"DEMOj.docs_load\"");
        Jxtp.tagClose("page");
    }

    public static void UPLOAD(Connection cn, byte[] binaryfiles, byte[] OK) throws SQLException {
        String docTbl = Jowa.GETCGIVAR("DOCUMENT_TABLE");
        PreparedStatement stmt = null;
        Jxtp.header("text/html");
        Jxtp.tagOpen("html");
        Jxtp.tag("h1", "Hello world");
        Jxtp.tagOpen("h2");
        Jxtp.tag("p", "This is upload procedure, written in Java Stored Procedures!");
        Jxtp.tag("p", "Upload succefull");
        Jxtp.tag("p", "Lenght =" + Jowa.GETCGIVAR("CONTENT_LENGTH"));
        try {
            JowaImage click = new JowaImage(OK);
            Jxtp.tag("p", click.toString());
        } catch (SQLException e) {
            Jxtp.p("Error casting image point arguments");
        }
        try {
            JowaArray msgArray = new JowaArray(binaryfiles);
            stmt = cn.prepareStatement("update " + docTbl + " set name = ? where name = ?");
            for (int i = 0; i < msgArray.getSize(); i++) { // rename uploaded files
                String oldName = msgArray.getValueAt(i);
                String newName = Jowa.GETCGIVAR("REMOTE_USER") + oldName.substring(oldName.indexOf('/'));
                stmt.setString(1, newName);
                stmt.setString(2, oldName);
                stmt.executeUpdate();
                Jxtp.tag("a", "uploaded file: " + newName,
                    "href=\"/dpls/java/" + Jowa.GETCGIVAR("DOC_ACCESS_PATH") + "/" + newName + "\"");
                Jxtp.tag("br", "");
            }
            stmt.close();
        } catch (Exception e) {
            Jxtp.tag("p", "Upload Exception: " + e.toString());
        }
        Jxtp.tagClose("h2");
        Jxtp.tag("a", "Wanna peek at what others have loaded...", "href=\"/dbprism/xmlj/DEMOj.docs_load\"");
        Jxtp.tagClose("html");
        Jxtp.epilog();
        stmt = null;
    }

    public static void OUTPUT(byte[] b, byte[] OK) throws UnsupportedEncodingException {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is my second Prism-Cocoon code with Java Stored Procedures!");
        try {
            JowaArray msgArray = new JowaArray(b);
            for (int i = 0; i < msgArray.getSize(); i++)
                Jxtp.tag("para", "msg line = " + i + " " + msgArray.getValueAt(i));
        } catch (Exception e) {
            Jxtp.tag("para", "Error in casting JowaArray argument");
        }
        //Jxtp.tag("para","b ="+b);
        Jxtp.tagOpen("para");
        Jxtp.p("image point coords (x,y) ");
        try {
            JowaImage click = new JowaImage(OK);
            Jxtp.p(click.toString());
        } catch (SQLException e) {
            Jxtp.p("Error casting image point arguments");
        }
        Jxtp.tagOpen("table");
        Enumeration cgiVars = Jowa.GETCGIVARS();
        while (cgiVars.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cgiVars.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", JowaUtil.getCgiVar(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.p("Cookies");
        Jxtp.tagOpen("para");
        Jxtp.tagOpen("table");
        Enumeration cookies = Jxtp.getAllCookies();
        while (cookies.hasMoreElements()) {
            Jxtp.tagOpen("tr");
            String name = (String)cookies.nextElement();
            Jxtp.tag("td", name);
            Jxtp.tag("td", Jxtp.getCookie(name));
            Jxtp.tagClose("tr");
        }
        Jxtp.tagClose("table");
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    public static void COOKIE() {
        Jxtp.header("text/xml", false);
        // Sent a cookie with an experitation equal to now + 1 day
        Jxtp.sendCookie("testjx", "2", new Date(System.currentTimeMillis() + (1000 * 60 * 60 * 24)));
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with Cookies");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with cookies, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void REDIRECT() {
        Jxtp.redirecUrl("http://xml.apache.org/");
    }

    public static void EXPIRES() {
        Jxtp.header("text/xml", false);
        Jxtp.setHeader("Cache-Control", "no-cache");
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with Cache-Control");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with Cache-Control Header, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public static void NOTAUTHORIZED() {
        Jxtp.notAuthorized("Test Java Adapter");
    }

    public static void STATUS() {
        Jxtp.header("text/xml", false);
        // Sent a cookie with an experitation equal to now + 1 day
        Jxtp.setHeader("Status", "200 Test Status");
        Jxtp.closeHeader();
        Jxtp.prolog();
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Hello world with Set Status");
        Jxtp.tagOpen("content");
        Jxtp.tag("paragraph", "This is my third Prism-Cocoon code with setStatus header, written in Java Stored Procedures!");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
        Jxtp.epilog();
    }

    public void EXCEL_query(Connection cn) throws SQLException {
        ResultSet rset = null;
        Statement stmt = null;
        // Create a Statement
        stmt = cn.createStatement();
        // Select the lobs
        rset = stmt.executeQuery("SELECT empno as EmployeeNumber, " + "       ename as Name, " + "       job as Title, " +
            "       sal as Salary" + "  FROM emp");
        Jxtp.tagOpen("ROWSET");
        while (rset.next()) {
            Jxtp.tagOpen("ROW", "empno=\"" + rset.getString(1) + "\"");
            Jxtp.tag("name", rset.getString(2));
            Jxtp.tag("job", rset.getString(3));
            Jxtp.tag("sal", rset.getString(4));
            Jxtp.tagClose("ROW");
        }
        Jxtp.tagClose("ROWSET");
        rset.close();
        rset = null;
        stmt.close();
        stmt = null;
    }

    public static void EXCEL(Connection cn) throws SQLException {
        Jxtp.prolog("1.0");
        //DefaultContext.setDefaultContext(new DefaultContext(new OracleDriver().defaultConnection()));
        DEMOj q = new DEMOj();
        q.EXCEL_query(cn);
    }

    public static void LIST(Connection cn) throws SQLException {
        // Create an instance of the OracleXMLQuery handler
        OracleXMLQuery q = new OracleXMLQuery(cn, "SELECT empno as EmployeeNumber, " + "       ename as Name, " +
            "       sal as Salary" + "  FROM emp");
        // Set some XML Generation options
        Jxtp.prolog();
        // Adds DOM tree
        Jxtp.p(q.getXMLDOM().getDocumentElement());
        Jxtp.epilog();
    }

    public static void LIST_JXTP(Connection cn) throws SQLException {
        ResultSet rset = null;
        Statement stmt = null;
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("ROWSET");
        int count = 1;
        for (int j = 0; j < 1; j++) {
            // Create a Statement
            stmt = cn.createStatement();
            // Select the lobs
            rset = stmt.executeQuery("SELECT empno as EmployeeNumber, " + "       ename as Name, " +
                "       sal as Salary" + "  FROM emp");
            while (rset.next()) {
                Jxtp.tagOpen("ROW", "NUM='" + count + "'");
                Jxtp.tag("EMPLOYEENUMBER", rset.getString(1));
                Jxtp.tag("NAME", rset.getString(2));
                Jxtp.tag("SALARY", rset.getString(3));
                Jxtp.tagClose("ROW");
                count++;
            }
            rset.close();
            stmt.close();
        }
        Jxtp.tagClose("ROWSET");
        Jxtp.epilog();
    }

    public static void FLEX(int num_entries, String reserved) throws SQLException {
        JowaArray nullArray = new JowaArray(0);
        FLEX(num_entries, nullArray.getBytes(), nullArray.getBytes(), reserved);
    }

    public static void FLEX(int num_entries, byte[] name_array, byte[] value_array, String reserved) {
        Jxtp.prolog("1.0");
        Jxtp.tagOpen("page");
        Jxtp.tag("title", "Output Form");
        Jxtp.tagOpen("content");
        Jxtp.tag("para", "This is my flexible parameters support for DB Prism-Cocoon code with Java Stored Procedures!");
        Jxtp.tag("para", "num_entries =" + num_entries);
        Jxtp.tagOpen("para");
        try {
            JowaArray names = new JowaArray(name_array);
            JowaArray values = new JowaArray(value_array);
            Jxtp.tagOpen("table");
            for (int i = 0; i < names.getSize(); i++) {
                Jxtp.tagOpen("tr");
                Jxtp.tag("td", names.getValueAt(i));
                Jxtp.tag("td", values.getValueAt(i));
                Jxtp.tagClose("tr");
            }
            Jxtp.tagClose("table");
        } catch (SQLException e) {
            Jxtp.p("Error casting array arguments");
        }
        Jxtp.tagClose("para");
        Jxtp.tagClose("content");
        Jxtp.tagClose("page");
    }

    public static void main(String[] args) {
      // TODO: write test case
    }
}
